import sys

from PySide6.QtCore import QSize
from PySide6.QtSql import QSqlDatabase, QSqlQueryModel, QSqlQuery
from PySide6.QtWidgets import (
    QApplication,
    QMainWindow,
    QHBoxLayout,
    QLineEdit,
    QTableView,
    QVBoxLayout,
    QWidget
)

class MainWindow(QMainWindow):
    def __init__(self):
        super().__init__()

        self.setWindowTitle("Python")

        container = QWidget()
        layout_search = QHBoxLayout()

        self.track = QLineEdit()
        self.track.textChanged.connect(self.update_query)
        self.track.setPlaceholderText("Track name...")
        layout_search.addWidget(self.track)

        self.composer = QLineEdit()
        self.composer.textChanged.connect(self.update_query)
        self.composer.setPlaceholderText("Artist name...")
        layout_search.addWidget(self.composer)

        self.album = QLineEdit()
        self.album.textChanged.connect(self.update_query)
        self.album.setPlaceholderText("Album name...")
        layout_search.addWidget(self.album)

        self.table = QTableView()
        layout = QVBoxLayout()
        layout.addLayout(layout_search)
        layout.addWidget(self.table)
        container.setLayout(layout)
        self.setMinimumSize(QSize(1024, 600))
        self.setCentralWidget(container)

        if self.initDb():
            self.model = QSqlQueryModel()
            self.table.setModel(self.model)

            self.query = QSqlQuery(db=self.db)
            self.query.prepare(
                "SELECT Name, Composer, Album.Title From Track "
                "INNER JOIN Album On Track.AlbumId = Album.AlbumId WHERE "
                "Track.Name LIKE '%' || :track_name || '%' AND "
                "Track.Composer LIKE '%' || :track_composer || '%' AND "
                "Album.Title LIKE '%' || :album_title || '%' "
            )            

            self.update_query()
            
    def update_query(self, s=None):
        track_name = self.track.text()
        track_composer = self.composer.text()
        album_title = self.album.text()

        self.query.bindValue(":track_name", track_name)
        self.query.bindValue(":track_composer", track_composer)
        self.query.bindValue(":album_title", album_title)
        self.query.exec()

        self.model.setQuery(self.query)

    def initDb(self):
        self.db = QSqlDatabase.addDatabase("QSQLITE")
        self.db.setDatabaseName("Chinook_Sqlite.sqlite")
        if not self.db.open():
            print("Cannot open database")
            return False
        
        return True

app = QApplication(sys.argv)
window = MainWindow()
window.show()
app.exec()